<?php

namespace App\Http\Controllers\Admin;

use App\Models\ClosingData;
use App\Models\Permissions;
use App\Models\Role;
use Illuminate\Http\Request;
use App\Http\Controllers\Controller;
use Illuminate\Support\Facades\Auth;
use Illuminate\Support\Facades\Cache;
use PhpOffice\PhpSpreadsheet\IOFactory;

class TestController extends Controller
{
    private  $file = null;
    public function __construct()
    {
        $this->middleware('auth:admin');
    }

    public function index(){
        $user = Auth::user();
        //$permissions  = $user->getAllPermissions();
        $role = Role::find(1);
        //$role->givePermissionTo('删除管理员');
        $res =$user->can('删除管理员', 'admin');
        //$res = $role->revokePermissionTo('删除管理员');
        dd($res);
    }

    /**
     * @return bool
     * @throws \PhpOffice\PhpSpreadsheet\Exception
     * @throws \PhpOffice\PhpSpreadsheet\Reader\Exception
     */
    public function cc(){
        $this->file = 'D:\我的文件\期货\测试.xlsx';
        if (!is_file($this->file)) {
            $msg = '没有找到该文件';
            echo $msg . "\n";
            return ;
        }
        $msg = '文件名：' . $this->file;
        echo $msg . "\n";
        if (!is_file($this->file)) {
            $msg = '没有找到该文件';
            echo $msg . "\n";
            return ;
        }

        if (strpos($this->file, 'xlsx') === false && strpos($this->file, 'xls') === false && strpos($this->file, 'csv') === false) {
            $msg = '文件后缀不合格，不予处理';
            echo $msg . "\n";
            return ;
        }

        $objReader = IOFactory::createReader('Xlsx');
        $objPHPExcel = $objReader->load($this->file);  //$filename可以是上传的表格，或者是指定的表格
        $objCount = $objPHPExcel->getSheetCount(); // sheet个数
        $data = [];  // 表格中获取的数据
        $date_time = date('Y-m-d');
        // 存在就覆盖，不存在添加
        $up_num = 0;
        $cr_num = 0;
        // 200条200条的存入数据库,防止数组过大
        $amount = 200;
        $times = 1;
        for ($i = 0; $i < $objCount; $i++) {
            $sheet = $objPHPExcel->getSheet($i);   //excel中的第i张sheet
            $highestRow = $sheet->getHighestRow();       // 取得总行数
            //循环读取excel表格，整合成数组。如果是不指定key的二维，就用$data[i][j]表示。
            for ($j = 2; $j <= $highestRow; $j++) {
                $oneData = [
                    'exchange_name' => $sheet->getCellByColumnAndRow(1, $j)->getValue(),
                    'exchange_code' => $sheet->getCellByColumnAndRow(2, $j)->getValue(),
                    'varietie_name' => $sheet->getCellByColumnAndRow(3, $j)->getValue(),
                    'varietie_code' => $sheet->getCellByColumnAndRow(4, $j)->getValue(),
                    'transaction' => $sheet->getCellByColumnAndRow(5, $j)->getValue(),
                    'closing_quotation' => $sheet->getCellByColumnAndRow(6, $j)->getValue(),
                    'deal_time' => date("Y-m-d", \PhpOffice\PhpSpreadsheet\Shared\Date::excelToTimestamp($sheet->getCellByColumnAndRow(7, $j)->getValue())),
                ];
                if (!is_numeric($oneData['transaction'])) {
                    $oneData['transaction'] = 0.00;
                } else {
                    $oneData['transaction'] = round($oneData['transaction'], 2);
                }
                if (!is_numeric($oneData['closing_quotation'])) {
                    $oneData['closing_quotation'] = 0.00;
                } else {
                    $oneData['closing_quotation'] = round($oneData['closing_quotation'], 2);
                }
                $oneData['deal_time_stamp'] = strtotime($oneData['deal_time']);
                if (!empty($oneData['exchange_code'])) {
                    $oneData['created_at'] = $date_time;
                    $data[] = $oneData;
                }
                if (count($data) >= $amount) {
                    $msg = '已累计' . $amount * $times . '条' . '，开始写入数据库';
                    echo $msg . "\n";
                    foreach ($data as $da) {
                        $info = ClosingData::query()
                            ->where('deal_time_stamp', $da['deal_time_stamp'])
                            ->where('exchange_code', $da['exchange_code'])
                            ->where('varietie_code', $da['varietie_code'])
                            ->first();
                        if ($info) {
                            $up_num++;
                            $info->transaction = $da['transaction'];
                            $info->closing_quotation = $da['closing_quotation'];
                            $info->save();
                        } else {
                            $cr_num++;
                            ClosingData::query()->create($da);
                        }
                    }
                    $msg = '清空$data，继续执行';
                    echo $msg . "\n";
                    $times += 1;
                    $data = [];
                }
            }
            // 不到$amount条的情况
            if(count($data) > 0){
                $msg = '不到'.$amount.'条';
                echo $msg . "\n";
                foreach ($data as $da) {
                    $info = ClosingData::query()
                        ->where('deal_time_stamp', $da['deal_time_stamp'])
                        ->where('exchange_code', $da['exchange_code'])
                        ->where('varietie_code', $da['varietie_code'])
                        ->first();
                    if ($info) {
                        $up_num++;
                        $info->transaction = $da['transaction'];
                        $info->closing_quotation = $da['closing_quotation'];
                        $info->save();
                    } else {
                        $cr_num++;
                        ClosingData::query()->create($da);
                    }
                }
                $data = [];
            }
        }
        $msg = '更新：' . $up_num . '条';
        echo $msg . "\n";
        $msg = '新增：' . $cr_num . '条';
        echo $msg . "\n";
        // 删除文件
        //unlink($this->file);
    }
}
